﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Threading;
using System.Configuration;

namespace ManagementProject
{
   
    public partial class frmCustomers : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        SqlCommandBuilder cb;
        DataSet ds;
        DataTable dt;
        DataView dv;
        bool change = false;

        public frmCustomers()
        {
            InitializeComponent();
            InitializeADOObject();
        }

        void InitializeADOObject()
        {
            try
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getCustomers";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds, "Sales.Customers");
                cboSearch.SelectedIndex = 0;
            }
            catch (Exception)
            {
                MessageBox.Show("Fail connection!");
            }
        }
        

        #region Validate, Load, Add, Update, Delete, Search
        bool validateInput()
        {
            errorProvider1.Clear();
            bool error = false;
            string s = "";
            s = txtCompanyName.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(txtCompanyName, "Please enter Companyname!");
                error = true;
            }
            if (s.Length > 40)
            {
                errorProvider1.SetError(txtCompanyName, "Companyname not greater than 40 character");
                error = true;
            }
            s = txtContactName.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(txtContactName, "Please enter ContactName!");
                error = true;
            }
            if (s.Length > 30)
            {
                errorProvider1.SetError(txtContactName, "Contactname not greater than 30 character");
                error = true;
            }
            s = cboContactTitle.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(cboContactTitle, "Please choice ContactTitle!");
                error = true;
            }
            s = txtAddress.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Please enter Address!");
                error = true;
            }
            if (s.Length > 60)
            {
                errorProvider1.SetError(txtAddress, "Address not greater than 60 character");
                error = true;
            }
            s = txtCity.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(txtCity, "Please enter City!");
                error = true;
            }
            if (s.Length > 15)
            {
                errorProvider1.SetError(txtCity, "City not greater than 15 character");
                error = true;
            }
            s = txtRegion.Text;
            if (s.Length > 15)
            {
                errorProvider1.SetError(txtRegion, "Region not greater than 15 character");
                error = true;
            }
            if ((mtxtPostalCode.Text.Length > 0) && (mtxtPostalCode.MaskCompleted == false))
            {
                errorProvider1.SetError(mtxtPostalCode, "please enter 5 digits or null");
                error = true;
            }
            s = cboCountry.Text;
            if (s.Trim().Length == 0)
            {
                errorProvider1.SetError(cboCountry, "Please choice Country!");
                error = true;
            }
            if (mtxtPhone.MaskCompleted == false)
            {
                errorProvider1.SetError(mtxtPhone, "please enter required digit!");
                error = true;
            }
            s = txtFax.Text;
            if (txtFax.MaskCompleted == false && s.Trim().Length == 0)
            {
                errorProvider1.SetError(txtFax, "please enter required digit!");
                error = true;
            }

            if (error == true)
            {
                return false;
            }
            else
                errorProvider1.Clear();
            return true;
        }

        void LoadCustomers()
        {

            try
            {
                dt = ds.Tables["Sales.Customers"];
                dv = new DataView(dt);
                gvCustomers.DataSource = dt;
            }
            catch (Exception e)
            {
                MessageBox.Show("ERORR!" + e.Message);
                gvCustomers = null;
            }


        }

        void addNewEmployee()
        {
            dt = ds.Tables["Sales.Customers"];
            DataRow r = dt.NewRow();
            r["companyname"] = txtCompanyName.Text;
            r["contactname"] = txtContactName.Text;
            r["contacttitle"] = cboContactTitle.Text;
            r["address"] = txtAddress.Text;
            r["city"] = txtCity.Text;
            if (txtRegion.Text != "")
                r["region"] = txtRegion.Text;
            else
                r["region"] = DBNull.Value; ;
            if (mtxtPostalCode.Text != "")
                r["postalcode"] = mtxtPostalCode.Text;
            else
                r["postalcode"] = DBNull.Value;
            r["country"] = cboCountry.Text;
            r["phone"] = mtxtPhone.Text;
            if (txtFax.Text == "")
                r["fax"] = txtFax.Text;
            else
                r["fax"] = DBNull.Value;
            dt.Rows.Add(r);
            try
            {
                da.Update(dt);
            }
            catch (Exception e)
            {
                MessageBox.Show("ERORR!" + e.Message);

            }
        }

        void updateEmployee()
        {
            try
            {
                if (gvCustomers.SelectedRows.Count > 0)
                {
                    errorProvider1.Clear();
                    dt = ds.Tables["Sales.Customers"];
                    DataGridViewRow rv = gvCustomers.SelectedRows[0];
                    DataRow[] r = dt.Select(string.Format("custid = '{0}'", rv.Cells["custid"].Value.ToString()));

                    r[0]["companyname"] = txtCompanyName.Text;
                    r[0]["contactname"] = txtContactName.Text;
                    r[0]["contacttitle"] = cboContactTitle.Text;
                    r[0]["address"] = txtAddress.Text;
                    r[0]["city"] = txtCity.Text;
                    if (txtRegion.Text != "")
                        r[0]["region"] = txtRegion.Text;
                    else
                        r[0]["region"] = DBNull.Value; ;
                    if (mtxtPostalCode.Text != "")
                        r[0]["postalcode"] = mtxtPostalCode.Text;
                    else
                        r[0]["postalcode"] = DBNull.Value;
                    
                    r[0]["country"] = cboCountry.Text;
                    r[0]["phone"] = mtxtPhone.Text;
                    if (txtFax.Text != "")
                        r[0]["fax"] = txtFax.Text;
                    else
                        r[0]["fax"] = DBNull.Value;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            

        }

        void deleteEmployee()
        {
            if (gvCustomers.SelectedRows.Count > 0)
            {
                DataGridViewRow rv = gvCustomers.SelectedRows[0];
                dv = new DataView(dt);
                dv.RowFilter = string.Format("custid = '{0}'", rv.Cells["custid"].Value.ToString());
                dv.Delete(0);
            }
        }

        void defaultEmployee()
        {
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            btnSavetoDatabase.Enabled = true;
            btnAdd.Enabled = true;
            txtID.Clear();
            txtCompanyName.Clear();
            txtContactName.Clear();
            cboContactTitle.SelectedIndex = -1;
            txtAddress.Clear();
            txtCity.Clear();
            txtRegion.Clear();
            mtxtPostalCode.Clear();
            cboCountry.SelectedIndex = -1;
            mtxtPhone.Clear();
            txtFax.Clear();
        }

        void SearchbyCompanyName()
        {

            dv = new DataView(dt);
            dv.RowFilter = string.Format("companyname like '%{0}%'", txtSearchInf.Text);
            gvCustomers.DataSource = dv;
        }

        void SearchbyContactName()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("contactname like '%{0}%'", txtSearchInf.Text);
            gvCustomers.DataSource = dv;
        }

        void SearchbyContactTitle()
        {

            dv = new DataView(dt);
            dv.RowFilter = string.Format("contacttitle = '{0}'", cboSearchTitle.Text);
            gvCustomers.DataSource = dv;
        }

        void SearchbyCountry()
        {

            dv = new DataView(dt);
            dv.RowFilter = string.Format("country = '{0}'", cboSearchCountry.Text);
            gvCustomers.DataSource = dv;
        }

        #endregion
        

        private void FrmCustomers_Load(object sender, EventArgs e)
        {
            LoadCustomers();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            defaultEmployee();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (!validateInput())
                return;
            else
            {
                try
                {
                    addNewEmployee();
                    InitializeADOObject();
                    LoadCustomers();
                    defaultEmployee();
                    MessageBox.Show("Add compelete ");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (!validateInput())
                return;
            else
            {
                try
                {
                    updateEmployee();
                    defaultEmployee();
                    change = true;
                    btnSavetoDatabase.Enabled = true;
                    MessageBox.Show("Update compelete ");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult rs = MessageBox.Show("Are you sure to delete!", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
            {
                try
                {
                    deleteEmployee();
                    da.Update(dt);
                    defaultEmployee();
                    change = true;
                    btnSavetoDatabase.Enabled = true;
                    MessageBox.Show("Delete compelete ");
                }
                catch (Exception ex)
                {
                    InitializeADOObject();
                    LoadCustomers();
                    MessageBox.Show("This item cannot delete!!" + ex.Message);
                    
                }
            }
        }

        private void btnSavetoDatabase_Click(object sender, EventArgs e)
        {
            DialogResult rs = MessageBox.Show("Are you sure to Save!", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
            {
                dt = ds.Tables["Sales.Customers"];
                try
                {
                    da.Update(dt);
                    change = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void gvCustomers_SelectionChanged(object sender, EventArgs e)
        {
            if (gvCustomers.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = gvCustomers.SelectedRows[0];
                txtID.Text = r.Cells["custid"].Value.ToString();
                txtCompanyName.Text = r.Cells["companyname"].Value.ToString();
                txtContactName.Text = r.Cells["contactname"].Value.ToString();
                cboContactTitle.Text = r.Cells["contacttitle"].Value.ToString();
                txtAddress.Text = r.Cells["address"].Value.ToString();
                txtCity.Text = r.Cells["city"].Value.ToString();
                txtRegion.Text = r.Cells["region"].Value.ToString();
                mtxtPostalCode.Text = r.Cells["postalcode"].Value.ToString();
                cboCountry.Text = r.Cells["country"].Value.ToString();
                mtxtPhone.Text = r.Cells["phone"].Value.ToString();
                txtFax.Text = r.Cells["fax"].Value.ToString();
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                string search = cboSearch.Text;
                if (search == "Company Name")
                {
                    SearchbyCompanyName();
                }
                else
                    if (search == "Contact Name")
                    {
                        SearchbyContactName();
                    }
                    else
                        if (search == "Contact Title")
                        {
                            SearchbyContactTitle();
                        }
                        else
                            if (search == "Country")
                            {
                                SearchbyCountry();
                            }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
               
           
        }

        private void btnShowAll_Click(object sender, EventArgs e)
        {
            try
            {
                gvCustomers.DataSource = dt;
                txtSearchInf.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void FrmCustomers_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (change)
            {
                DialogResult rs = MessageBox.Show("Save Change to Database?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
                if (rs == DialogResult.Yes)
                {
                    dt = ds.Tables["Sales.Customers"];
                    try
                    {
                        da.Update(dt);
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(ex.Message);
                    }
                }
                else
                    if (rs == DialogResult.Cancel)
                        e.Cancel = true;
            }
        }

        private void cbSearch_SelectedIndexChanged(object sender, EventArgs e)
        {
            string search = cboSearch.Text;
            if (search == "Company Name" || search == "Contact Name")
            {
                txtSearchInf.Visible = true;
                cboSearchCountry.Visible = false;
                cboSearchTitle.Visible = false;
            }
            else
            if (search == "Contact Title")
            {
                cboSearchCountry.Visible = false;
                txtSearchInf.Visible = false;
                cboSearchTitle.Visible = true;
            }
            else
                if (search == "Country")
                {
                    cboSearchTitle.Visible = false;
                    cboSearchCountry.Visible = true;
                    txtSearchInf.Visible = false;
                }
        }   
    }  
}
